package newsSystem.dao;

import newsSystem.user.UserInfo;
import newsSystem.user.UserManager;
import newsSystem.utils.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserInfoJDBC {



//查询所有用户信息操作
    public static void UserJDBC() throws Exception{
//public static void main(String[] args)throws Exception {
        //    调用Util里面连接数据库中的类
        Connection connection = null;
        ResultSet resultSet = null;
        Statement statement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 查询语句
            String sql = "SELECT * FROM userinfo";

            // 创建 Statement 对象执行查询
            statement = connection.createStatement();

            // 执行查询并获取结果集合
            resultSet = statement.executeQuery(sql);
//         创建一个管理员list列表对象
            List<UserInfo> UserList=new ArrayList<>();
            // 处理结果集合
            while (resultSet.next()) {
                // 获取每一行数据的具体字段值
// 获取用户ID
                String userID = resultSet.getString("UserID");
// 获取部门ID
//                int deptID = resultSet.getInt("DeptID");
// 获取用户密码
                String userPWD = resultSet.getString("UserPWD");
// 获取用户名
                String userName = resultSet.getString("UserName");
// 获取用户性别
                String userSex = resultSet.getString("UserSex");
// 获取身份证号
                String idCard = resultSet.getString("IDCard");
// 获取电话号码
                String phone = resultSet.getString("Phone");
// 获取地址
                String address = resultSet.getString("Address");


                // 打印结果
//                System.out.println("用户ID: " + userID + ", 部门ID: " + deptID + ", 用户密码: " + userPWD + ", 用户名: " + userName + ", 用户性别: " + userSex + ", 身份证号: " + idCard + ", 电话号码: " + phone + ", 地址: " + address);

//            创建管理员对象并且将其添加到集合中去
                UserInfo user=new UserInfo(userID,userPWD,userName,userSex,idCard,phone,address);
//                调用AdminManager类中的添加add方法加入到列表中
                UserManager.addUser(user);
            }

        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
//            连接结束后释放资源
            DBUtil.closeConnection(connection);
//            释放结果集合
            DBUtil.closeResultSet(resultSet);
//            释放Statement 对象
            DBUtil.closePreparedStatement(statement);
        }
    }
//    查询所有用户操作
    public static void addUsers(List<UserInfo> userList) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 插入语句
            String sql = "INSERT INTO userinfo (UserID, UserPWD, UserName, UserSex, IDCard, Phone, Address) VALUES (?, ?, ?, ?, ?, ?, ?)";

            // 创建 PreparedStatement 对象执行插入
            preparedStatement = connection.prepareStatement(sql);

            // 遍历用户列表，逐个添加用户信息
            for (UserInfo user : userList) {
                // 设置参数值
                preparedStatement.setString(1, user.getUserID());
                preparedStatement.setString(2, user.getUserPWD());
                preparedStatement.setString(3, user.getUserName());
                preparedStatement.setString(4, user.getUserSex());
                preparedStatement.setString(5, user.getIDCard());
                preparedStatement.setString(6, user.getPhone());
                preparedStatement.setString(7, user.getAddress());

                // 执行插入操作
                preparedStatement.executeUpdate();
            }

            System.out.println("用户信息添加成功！");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }

//删除用户操作
    public static void deleteUserFromDatabase(String userID) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 删除语句
            String sql = "DELETE FROM userinfo WHERE UserID = ?";

            // 使用 PreparedStatement 预编译 SQL 语句
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数值
            preparedStatement.setString(1, userID);

            // 执行删除操作
            int rowsAffected = preparedStatement.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("成功删除用户 " + userID);
            } else {
                System.out.println("未找到用户 " + userID);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }
//根据用户UserID进行查询用户信息操作
public static UserInfo getUserInfoFromDatabase(String userID) {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    UserInfo userInfo = null;
    try {
        // 获取数据库连接
        connection = DBUtil.getConnection();

        // 定义 SQL 查询语句
        String sql = "SELECT * FROM userinfo WHERE UserID = ?";

        // 使用 PreparedStatement 预编译 SQL 语句
        preparedStatement = connection.prepareStatement(sql);

        // 设置参数值
        preparedStatement.setString(1, userID);

        // 执行查询操作
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            // 用户存在，将用户信息存入UserInfo对象
            String username = resultSet.getString("UserName");
            String userpassword = resultSet.getString("UserPWD");
            String userSex = resultSet.getString("UserSex");
            String idCard = resultSet.getString("IDCard");
            String phone = resultSet.getString("Phone");
            String address = resultSet.getString("Address");

            userInfo = new UserInfo(userID, userpassword, username, userSex, idCard, phone, address);
        } else {
            // 用户不存在，返回null
            System.out.println("未找到用户 " + userID);
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // 释放资源
        DBUtil.closeResultSet(resultSet);
        DBUtil.closePreparedStatement(preparedStatement);
        DBUtil.closeConnection(connection);
    }
    return userInfo;
}

//修改数据库操作
public static void updateUserInDatabase(UserInfo updatedUserInfo) throws SQLException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        // 获取数据库连接
        connection = DBUtil.getConnection();

        // 定义 SQL 更新语句，这里假设 UserID 是主键
        String sql = "UPDATE userinfo SET UserPWD=?, UserName=?, UserSex=?, IDCard=?, Phone=?, Address=? WHERE UserID=?";

        // 创建 PreparedStatement 对象执行更新
        preparedStatement = connection.prepareStatement(sql);

        // 绑定参数
        preparedStatement.setString(1, updatedUserInfo.getUserPWD());
        preparedStatement.setString(2, updatedUserInfo.getUserName());
        preparedStatement.setString(3, updatedUserInfo.getUserSex());
        preparedStatement.setString(4, updatedUserInfo.getIDCard());
        preparedStatement.setString(5, updatedUserInfo.getPhone());
        preparedStatement.setString(6, updatedUserInfo.getAddress());
        preparedStatement.setString(7, updatedUserInfo.getUserID()); // UserID 作为主键进行匹配更新

        // 执行更新操作
        int rowsAffected = preparedStatement.executeUpdate();

        // 如果受影响的行数大于0，则表示更新成功
        if (rowsAffected > 0) {
            System.out.println("用户信息更新成功！");
        } else {
            System.out.println("用户信息未更新，可能是因为提供的 UserID 不存在或者信息没有变化。");
        }

    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    } finally {
        // 关闭资源
        DBUtil.closeConnection(connection);
        DBUtil.closePreparedStatement(preparedStatement);
    }
}
//添加单个用户操作

public static void addUserByUserInfo(UserInfo newUser) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            // 获取数据库连接
            connection = DBUtil.getConnection();

            // 定义 SQL 插入语句
            String sql = "INSERT INTO userinfo (UserID, UserPWD, UserName, UserSex, IDCard, Phone, Address) VALUES (?, ?, ?, ?, ?, ?, ?)";

            // 创建 PreparedStatement 对象执行插入
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数值
            preparedStatement.setString(1, newUser.getUserID());
            preparedStatement.setString(2, newUser.getUserPWD());
            preparedStatement.setString(3, newUser.getUserName());
            preparedStatement.setString(4, newUser.getUserSex());
            preparedStatement.setString(5, newUser.getIDCard());
            preparedStatement.setString(6, newUser.getPhone());
            preparedStatement.setString(7, newUser.getAddress());

            // 执行插入操作
            preparedStatement.executeUpdate();

            System.out.println("用户信息添加成功！");
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            // 释放资源
            DBUtil.closeConnection(connection);
            DBUtil.closePreparedStatement(preparedStatement);
        }
    }
}
